﻿Imports Mysoft.Map.Utility
Imports System.Data.SqlClient
Imports Mysoft.Map.Data

Imports System.Xml
Imports System.Text
Imports System.Text.RegularExpressions

Public Class Activity_Preview_Grid
    Inherits Mysoft.Map.Application.Security.AppPage

    '初始化
    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        If Page.IsPostBack = False Then
            Dim strSql As String = Request.Form("txtSqlStr")
            Dim strFilterStr As String = Request.Form("txtFilterStr")
            If strFilterStr Is Nothing Then
                strFilterStr = ""
            Else
                strFilterStr = strFilterStr.Replace("ep_customer_zl", "ep_customer")
            End If
            Dim strMainTable As String = Request.Form("txtMainTable")
            If strMainTable Is Nothing Then
                strMainTable = ""
            Else
                strMainTable = strMainTable.ToLower().Replace("ep_customer_zl", "ep_customer")
            End If

            strMainTable.Replace("ep_customer_zl", "ep_customer")
            txtSql.Text = GetSqlString(strMainTable, Request.QueryString("txtType"), strSql, strFilterStr)
            Session("txtDetailSql") = GetDetailSql(strMainTable, Request.Form("txtDetailSql"), strFilterStr)
            MainBindDataGrid()
            lbtnFirst.Enabled = False
            lbtnPrev.Enabled = False
            txtFilter.Text = XML2Filter1(strFilterStr).Replace("andand", "and").Replace("and and", "and").Replace("and  and", "and").Replace("and", " and ").Replace("andOR", "OR").Replace("and OR", "OR").Replace("and  OR", "OR").Replace("and   OR", "OR").Replace("OR", " OR ").Replace("and  OR", "OR").Replace("and  )", ")") & "(1=1)"

            If lblTotal.Text = "0" AndAlso Request.QueryString("btn") = "preview" Then
                Literal1.Text = "<Script>alert('没有符合条件的记录！');</Script>"
            End If
        End If

    End Sub

    '首页
    Private Sub lbtnFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lbtnFirst.Click
        dgdMain.CurrentPageIndex = 0
        lbtnFirst.Enabled = False
        lbtnPrev.Enabled = False
        lbtnNext.Enabled = True
        lbtnLast.Enabled = True
        MainBindDataGrid()
    End Sub

    '上页
    Private Sub lbtnPrev_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lbtnPrev.Click
        Dim intCurrentPage As Integer = dgdMain.CurrentPageIndex
        dgdMain.CurrentPageIndex = intCurrentPage - 1
        If intCurrentPage = 1 Then
            lbtnFirst.Enabled = False
            lbtnPrev.Enabled = False
        End If
        lbtnNext.Enabled = True
        lbtnLast.Enabled = True
        MainBindDataGrid()
    End Sub

    '下页
    Private Sub lbtnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lbtnNext.Click
        Dim intCurrentPage As Integer = dgdMain.CurrentPageIndex
        dgdMain.CurrentPageIndex = intCurrentPage + 1
        If intCurrentPage = dgdMain.PageCount - 2 Then
            lbtnNext.Enabled = False
            lbtnLast.Enabled = False
        End If
        lbtnFirst.Enabled = True
        lbtnPrev.Enabled = True
        MainBindDataGrid()
    End Sub

    '尾页
    Private Sub lbtnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lbtnLast.Click
        dgdMain.CurrentPageIndex = dgdMain.PageCount - 1
        lbtnNext.Enabled = False
        lbtnLast.Enabled = False
        lbtnFirst.Enabled = True
        lbtnPrev.Enabled = True
        MainBindDataGrid()
    End Sub

    '升降排序
    Private Sub dgdMain_SortCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridSortCommandEventArgs) Handles dgdMain.SortCommand
        txtSortField.Text = e.SortExpression
        If (txtSortMode.Text = "DESC") Then
            txtSortMode.Text = "ASC"
        Else
            txtSortMode.Text = "DESC"
        End If
        MainBindDataGrid()
    End Sub

    '装载查询结果
    Private Sub MainBindDataGrid()
        Dim sadMainBind As New SqlDataAdapter(txtSql.Text, MyDB.GetSqlConnectionString)
        Dim dsMainBind As New DataSet
        Dim tmpTable As DataTable

        Try
            sadMainBind.Fill(dsMainBind)
            tmpTable = dsMainBind.Tables(0)
        Catch
            tmpTable = New DataTable
            tmpTable.Columns.Add("重复")
            tmpTable.Columns.Add("客户名称")
            If Request.QueryString("txtType") = "短信" Then
                tmpTable.Columns.Add("手机号码")
            Else
                tmpTable.Columns.Add("Email")
            End If
        End Try

        Dim dvMainBind As DataView = tmpTable.DefaultView
        Dim intCurrentPageIndex As Integer = dgdMain.CurrentPageIndex
        Dim intRecordCount As Integer = dvMainBind.Count
        Dim intPageCount As Integer = intRecordCount \ 15
        If intRecordCount Mod 15 <> 0 Then
            intPageCount += 1
        End If
        If intCurrentPageIndex > intPageCount - 1 Then
            If intPageCount = 0 Then
                intCurrentPageIndex = 0
            Else
                intCurrentPageIndex = intPageCount - 1
            End If
        End If

        dgdMain.CurrentPageIndex = intCurrentPageIndex
        dvMainBind.Sort = txtSortField.Text & " " & txtSortMode.Text
        dgdMain.DataSource = dvMainBind
        dgdMain.DataBind()
        lblTotal.Text = intRecordCount.ToString()             '总任务数
        lblCurrentPage.Text = CStr(intCurrentPageIndex + 1) & "/" & intPageCount.ToString() '当前页
        If intRecordCount = 0 Then lblCurrentPage.Text = "0/0"
        If intPageCount <= 1 Then        '只有一页时
            lbtnFirst.Enabled = False
            lbtnPrev.Enabled = False
            lbtnNext.Enabled = False
            lbtnLast.Enabled = False
        End If
    End Sub

    '详情
    Private Function GetDetailSql(ByVal strMainTable As String, ByVal strSql As String, ByVal strFilterStr As String) As String
        If strMainTable Is Nothing Then
            Return ""
        End If

        If strMainTable.ToLower = "ep_customer" Or strMainTable.ToLower = "ep_customer_zl" Then

            strSql = strSql & " where " & strMainTable & ".cstguid in (select cstguid from p_CstAttach where buguid='" & Session("BUGUID") & "') AND isreceivesms=1 and (" & General.XML2Filter(strFilterStr) & ")"

        Else

            strSql = strSql & " where buguid='" & Session("BUGUID") & "' and (" & General.XML2Filter(strFilterStr) & ")"

        End If

        Return strSql
    End Function

    '返回DataView
    Private Function GetSqlString(ByVal strMainTable As String, ByVal strActType As String, ByVal strSql As String, ByVal strFilterStr As String) As String
        If strActType = "短信" Then
            strActType = "手机号码"
        Else
            strActType = "Email"
        End If

        If strSql = "" Then
            Return "select newid() as id, '' as [重复],'' as [客户名称],'' as " & strActType & " where 1=2"
        End If

        If strMainTable Is Nothing Then
            Return ""
        End If

        If Request.QueryString("From").ToString = "KHGH" Then

            If strMainTable.ToLower = "ep_customer" Or strMainTable.ToLower = "ep_customer_zl" Then

                strSql = strSql & " where " & strMainTable & ".cstguid in (select cstguid from p_CstAttach where buguid='" & Session("BUGUID") & "') and " & XML2Filter1(strFilterStr).Replace("andand", "and").Replace("and and", "and").Replace("and  and", "and").Replace("and", " and ").Replace("andOR", "OR").Replace("and OR", "OR").Replace("and  OR", "OR").Replace("and   OR", "OR").Replace("OR", " OR ").Replace("and  OR", "OR").Replace("and  )", ")") & "(1=1)"

                strSql = "declare @str varchar(50);" & strSql & " AND isreceivesms=1;delete #ActivityPreviewTmp where #ActivityPreviewTmp.id<>(select top 1 id from #ActivityPreviewTmp d where d.[客户名称] = #ActivityPreviewTmp.[客户名称] and d." & strActType & " = #ActivityPreviewTmp." & strActType & ");select * into #ActivityPreviewTmpLS from #ActivityPreviewTmp where 1=2;"
                strSql += "delete #ActivityPreviewTmp where [" & strActType & "]='' or [" & strActType & "] is null;"
                strSql += "update #ActivityPreviewTmp set [重复]='*' where [" & strActType & "] in (select [" & strActType & "] from #ActivityPreviewTmp group by [" & strActType & "] having count(1)>1);"
                strSql += "DECLARE Cursor_Title CURSOR FOR SELECT distinct [" & strActType & "] FROM #ActivityPreviewTmp;"
                strSql += "OPEN Cursor_Title;"
                strSql += "FETCH NEXT FROM Cursor_Title Into @str;"
                strSql += "WHILE @@FETCH_STATUS=0 "
                strSql += "BEGIN "
                strSql += "insert into #ActivityPreviewTmpLS select top 1 * from #ActivityPreviewTmp where [" & strActType & "]=@str;"
                strSql += "FETCH NEXT FROM Cursor_Title Into @str;"
                strSql += "End "
                strSql += "CLOSE Cursor_Title;DEALLOCATE Cursor_Title;select * from #ActivityPreviewTmpLS;drop table #ActivityPreviewTmp;drop table #ActivityPreviewTmpLS;"

            Else

                strSql = strSql & " where buguid='" & Session("BUGUID") & "' and " & XML2Filter1(strFilterStr).Replace("andand", "and").Replace("and and", "and").Replace("and  and", "and").Replace("and", " and ").Replace("andOR", "OR").Replace("and OR", "OR").Replace("and  OR", "OR").Replace("and   OR", "OR").Replace("OR", " OR ").Replace("and  OR", "OR").Replace("and  )", ")") & "(1=1)"

                strSql = "declare @str varchar(50);" & strSql & ";delete #ActivityPreviewTmp where #ActivityPreviewTmp.id<>(select top 1 id from #ActivityPreviewTmp d where d.[客户名称] = #ActivityPreviewTmp.[客户名称] and d." & strActType & " = #ActivityPreviewTmp." & strActType & ");select * into #ActivityPreviewTmpLS from #ActivityPreviewTmp where 1=2;"
                strSql += "delete #ActivityPreviewTmp where [" & strActType & "]='' or [" & strActType & "] is null;"
                strSql += "update #ActivityPreviewTmp set [重复]='*' where [" & strActType & "] in (select [" & strActType & "] from #ActivityPreviewTmp group by [" & strActType & "] having count(1)>1);"
                strSql += "DECLARE Cursor_Title CURSOR FOR SELECT distinct [" & strActType & "] FROM #ActivityPreviewTmp;"
                strSql += "OPEN Cursor_Title;"
                strSql += "FETCH NEXT FROM Cursor_Title Into @str;"
                strSql += "WHILE @@FETCH_STATUS=0 "
                strSql += "BEGIN "
                strSql += "insert into #ActivityPreviewTmpLS select top 1 * from #ActivityPreviewTmp where [" & strActType & "]=@str;"
                strSql += "FETCH NEXT FROM Cursor_Title Into @str;"
                strSql += "End "
                strSql += "CLOSE Cursor_Title;DEALLOCATE Cursor_Title;select * from #ActivityPreviewTmpLS;drop table #ActivityPreviewTmp;drop table #ActivityPreviewTmpLS;"

            End If

        ElseIf (Request.QueryString("From").ToString = "KHTZ" Or Request.QueryString("From").ToString = "YWCS") Then

            If strMainTable.ToLower = "ep_customer" Or strMainTable.ToLower = "ep_customer_zl" Then

                strSql = strSql & " where " & strMainTable & ".cstguid in (select cstguid from p_CstAttach where buguid='" & Session("BUGUID") & "') and (" & General.XML2Filter(strFilterStr) & ")"

                strSql = "declare @str varchar(50);" & strSql & " AND isreceivesms=1;delete #ActivityPreviewTmp where #ActivityPreviewTmp.id<>(select top 1 id from #ActivityPreviewTmp d where d.[客户名称] = #ActivityPreviewTmp.[客户名称] and d." & strActType & " = #ActivityPreviewTmp." & strActType & ");"


                strSql += "select * into #ActivityPreviewTmpLS  from #ActivityPreviewTmp;select * from #ActivityPreviewTmpLS;drop table #ActivityPreviewTmp;drop table #ActivityPreviewTmpLS;"

            Else

                strSql = strSql & " where buguid='" & Session("BUGUID") & "' and (" & General.XML2Filter(strFilterStr) & ")"

                strSql = "declare @str varchar(50);" & strSql & ";delete #ActivityPreviewTmp where [客户名称] is null;delete #ActivityPreviewTmp where #ActivityPreviewTmp.id<>(select top 1 id from #ActivityPreviewTmp d where d.[客户名称] = #ActivityPreviewTmp.[客户名称] and d." & strActType & " = #ActivityPreviewTmp." & strActType & ");"

                strSql += "select * into #ActivityPreviewTmpLS  from #ActivityPreviewTmp;select * from #ActivityPreviewTmpLS;drop table #ActivityPreviewTmp;drop table #ActivityPreviewTmpLS;"

            End If

        End If
        
        '如果是Email或者是由客户台帐或者是由业务参数设置进入的则无需过滤这个选项。
        If strActType = "Email" Or Request.QueryString("From").ToString = "KHTZ" Or Request.QueryString("From").ToString = "YWCS" Then
            strSql = strSql.Replace(" AND isreceivesms=1", "")
        End If

        Return strSql
    End Function

    '绑定事件
    Private Sub dgdMain_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs) Handles dgdMain.ItemDataBound
        If e.Item.Cells(0).Text = "*" Then
            e.Item.Attributes.Add("onclick", "javascript:RepeatClick('" & e.Item.Cells(2).Text & "');")
        End If
    End Sub

    Public Function XML2Filter1(ByVal xml As String, Optional ByVal entityName As String = "", Optional ByVal primaryKey As String = "", Optional ByVal strBUGUID As String = "", Optional ByVal strUserGUID As String = "") As String
        '先替换关键字，避免多次编译执行
        If xml Is Nothing Then Return "1=2"
        xml = ReplaceKeywords2(xml, strBUGUID, strUserGUID)

        Dim xmlQuery As New XmlDocument
        Try
            xmlQuery.LoadXml(xml)
        Catch ex As System.Exception
            Return "1=2"
        End Try

        If xmlQuery.DocumentElement.Name = "filter" Then
            Return GetFilter(xmlQuery.DocumentElement, entityName, strBUGUID, strUserGUID)
        End If

        Dim xmlLinks As XmlNodeList
        Dim xmlN As XmlNode
        Dim i As Integer
        xmlLinks = xmlQuery.DocumentElement.SelectNodes("link-entity")

        If xmlLinks.Count = 0 Then
            xmlN = xmlQuery.DocumentElement.SelectSingleNode("filter")
            If xmlN Is Nothing Then
                Return ""
            Else
                Return GetFilter(xmlN, entityName, strBUGUID, strUserGUID)
            End If
        End If

        Dim strEntityName As String     '传入的实体名，可能为别名
        strEntityName = entityName
        xmlN = xmlQuery.DocumentElement.Attributes.GetNamedItem("name")
        If xmlN Is Nothing OrElse xmlN.Value = "" Then Return "1=2"
        entityName = xmlN.Value
        If strEntityName = String.Empty Then strEntityName = entityName

        xmlN = xmlQuery.DocumentElement.Attributes.GetNamedItem("primarykey")
        If xmlN Is Nothing OrElse xmlN.Value = "" Then
            primaryKey = entityName & "GUID"
        Else
            primaryKey = xmlN.Value
        End If
        'If entityName = "" Then Return "1=2"
        'If primaryKey = "" Then primaryKey = entityName & "GUID"

        Dim sbFilter As StringBuilder
        Dim strTemp As String
        'Dim strentityNameTmp As String
        'strentityNameTmp = "tb_" & Guid.NewGuid.ToString("N")

        '关联实体属性
        Dim strName, strFrom, strTo, strPrimaryKey As String
        Dim strAlias As String      '增加别名，以避免关联多个同名实体时出错

        sbFilter = New StringBuilder
        '''''sbFilter.Append("select " & strentityNameTmp & "." & primaryKey & " from " & entityName & " " & strentityNameTmp)

        For i = 0 To xmlLinks.Count - 1
            strName = xmlLinks(i).Attributes.GetNamedItem("name").Value
            strAlias = "tb_" & Guid.NewGuid.ToString("N")
            strFrom = xmlLinks(i).Attributes.GetNamedItem("from").Value
            strTo = xmlLinks(i).Attributes.GetNamedItem("to").Value
            If xmlLinks(i).Attributes.GetNamedItem("primarykey") Is Nothing Then
                strPrimaryKey = ""
            Else
                strPrimaryKey = xmlLinks(i).Attributes.GetNamedItem("primarykey").Value
            End If

            '''''sbFilter.Append(" inner join " & strName & " " & strAlias)
            '''''sbFilter.Append(" on " & strentityNameTmp & "." & strTo & " = " & strAlias & "." & strFrom)

            '递归
            strTemp = XML2Filter1(xmlLinks(i).OuterXml, strName, strPrimaryKey, strBUGUID, strUserGUID)
            If strTemp <> "1=1" Then
                sbFilter.Append(strTemp & "and")
            End If
        Next

        xmlN = xmlQuery.DocumentElement.SelectSingleNode("filter")
        If Not xmlN Is Nothing Then
            strTemp = GetFilter(xmlN, entityName, strBUGUID, strUserGUID)
            If strTemp <> "1=1" Then
                sbFilter.Append(strTemp & "and")
            End If
        End If

        ' '' ''sbFilter.Insert(0, strEntityName & "." & primaryKey & " in (")
        ' '' ''sbFilter.Append(")")

        Return sbFilter.ToString
    End Function

    Private Function GetFilter(ByRef xmlN As XmlNode, ByVal strEntity As String, ByVal strBUGUID As String, ByVal strUserGUID As String) As String
        Dim i As Integer
        Dim xmlNList As XmlNodeList
        Dim sbFilter As StringBuilder
        Dim strType As String
        Dim strDataType As String
        Dim strTemp As String
        Dim strField, strValue As String

        xmlNList = xmlN.SelectNodes("condition|filter")
        If xmlNList.Count = 0 Then
            Return "1=1"
        End If

        '过滤条件拼接类型
        If Not xmlN.Attributes.GetNamedItem("type") Is Nothing AndAlso _
            xmlN.Attributes.GetNamedItem("type").Value.ToUpper = "OR" Then
            strType = "OR"
        Else
            strType = "and"
        End If

        sbFilter = New StringBuilder
        sbFilter.Append("(")
        For i = 0 To xmlNList.Count - 1
            If i > 0 Then

                sbFilter.Append(strType)

            End If

            '字段名
            If xmlNList(i).Attributes.GetNamedItem("attribute") Is Nothing Then
                strField = ""
            Else
                strField = xmlNList(i).Attributes.GetNamedItem("attribute").Value
            End If
            '如果过滤字段不是  <表名>.<字段名>  格式，且不是数字，在前面加  <表名>.  前缀
            '如果过滤字段为数字，表示为特殊处理的条件
            If strEntity <> "" AndAlso strField.IndexOf(".") < 0 AndAlso Not IsNumeric(strField) Then
                strField = strEntity & "." & strField
            End If

            '数据类型
            If Not xmlNList(i).Attributes.GetNamedItem("datatype") Is Nothing Then
                strDataType = xmlNList(i).Attributes.GetNamedItem("datatype").Value
            Else
                strDataType = ""
            End If

            Select Case xmlNList(i).Name.ToLower
                Case "condition"


                    Select Case xmlNList.Item(i).Attributes.GetNamedItem("operator").Value.ToLower
                        Case "tempvalue"        '临时变量
                            Dim strTempValue As String
                            strTempValue = MyDB.GetDataItemString("SELECT TempValue FROM myTemp WHERE TempGUID = '" & xmlNList(i).Attributes.GetNamedItem("value").Value.Replace("'", "''") & "'")
                            sbFilter.Append(XML2Filter1(strTempValue, strEntity, strBUGUID, strUserGUID))

                        Case "api"              '调用函数
                            sbFilter.Append( _
                               getQueryString(xmlNList(i).Attributes.GetNamedItem("attribute").Value, _
                                xmlNList(i).Attributes.GetNamedItem("value").Value, _
                                xmlNList(i).Attributes.GetNamedItem("datatype").Value, strBUGUID, strUserGUID) _
                            )

                        Case "eq"               '等于
                            sbFilter.Append(strField)
                            sbFilter.Append("=")
                            'money类型的数据不能从字符串隐式转换
                            If strDataType = "money" Then
                                sbFilter.Append(Decimal.Parse(xmlNList(i).Attributes.GetNamedItem("value").Value).ToString)
                            Else
                                sbFilter.Append("'")
                                sbFilter.Append(xmlNList(i).Attributes.GetNamedItem("value").Value.Replace("'", "''"))
                                sbFilter.Append("'")
                            End If
                        Case "ne"               '不等于
                            sbFilter.Append(strField)
                            sbFilter.Append("<>")
                            If strDataType = "money" Then
                                sbFilter.Append(Decimal.Parse(xmlNList(i).Attributes.GetNamedItem("value").Value).ToString)
                            Else
                                sbFilter.Append("'")
                                sbFilter.Append(xmlNList(i).Attributes.GetNamedItem("value").Value.Replace("'", "''"))
                                sbFilter.Append("'")
                            End If
                        Case "gt"               '大于
                            sbFilter.Append(strField)
                            sbFilter.Append(">")
                            If strDataType = "money" Then
                                sbFilter.Append(Decimal.Parse(xmlNList(i).Attributes.GetNamedItem("value").Value).ToString)
                            Else
                                sbFilter.Append("'")
                                sbFilter.Append(xmlNList(i).Attributes.GetNamedItem("value").Value.Replace("'", "''"))
                                sbFilter.Append("'")
                            End If
                        Case "ge"               '大于等于
                            sbFilter.Append(strField)
                            sbFilter.Append(">=")
                            If strDataType = "money" Then
                                sbFilter.Append(Decimal.Parse(xmlNList(i).Attributes.GetNamedItem("value").Value).ToString)
                            Else
                                sbFilter.Append("'")
                                sbFilter.Append(xmlNList(i).Attributes.GetNamedItem("value").Value.Replace("'", "''"))
                                sbFilter.Append("'")
                            End If
                        Case "lt"               '小于
                            sbFilter.Append(strField)
                            sbFilter.Append("<")
                            If strDataType = "money" Then
                                sbFilter.Append(Decimal.Parse(xmlNList(i).Attributes.GetNamedItem("value").Value).ToString)
                            Else
                                sbFilter.Append("'")
                                sbFilter.Append(xmlNList(i).Attributes.GetNamedItem("value").Value.Replace("'", "''"))
                                sbFilter.Append("'")
                            End If
                        Case "le"               '小于等于
                            sbFilter.Append(strField)
                            sbFilter.Append("<=")
                            If strDataType = "money" Then
                                sbFilter.Append(Decimal.Parse(xmlNList(i).Attributes.GetNamedItem("value").Value).ToString)
                            Else
                                sbFilter.Append("'")
                                sbFilter.Append(xmlNList(i).Attributes.GetNamedItem("value").Value.Replace("'", "''"))
                                sbFilter.Append("'")
                            End If
                        Case "like"             '包含
                            sbFilter.Append(strField)
                            sbFilter.Append(" like '%")
                            sbFilter.Append(xmlNList(i).Attributes.GetNamedItem("value").Value.Replace("'", "''"))
                            sbFilter.Append("%'")
                        Case "llike"            '前面自动加“%”的包含
                            sbFilter.Append(strField)
                            sbFilter.Append(" like '%")
                            sbFilter.Append(xmlNList(i).Attributes.GetNamedItem("value").Value.Replace("'", "''"))
                            sbFilter.Append("'")
                        Case "rlike"            '后面自动加“%”的包含
                            sbFilter.Append(strField)
                            sbFilter.Append(" like '")
                            sbFilter.Append(xmlNList(i).Attributes.GetNamedItem("value").Value.Replace("'", "''"))
                            sbFilter.Append("%'")
                        Case "nlike"            '前后都不自动加“%”的包含
                            sbFilter.Append(strField)
                            sbFilter.Append(" like '")
                            sbFilter.Append(xmlNList(i).Attributes.GetNamedItem("value").Value.Replace("'", "''"))
                            sbFilter.Append("'")
                        Case "not-like"         '不包含
                            sbFilter.Append("NOT ")
                            sbFilter.Append(strField)
                            sbFilter.Append(" like '%")
                            sbFilter.Append(xmlNList(i).Attributes.GetNamedItem("value").Value.Replace("'", "''"))
                            sbFilter.Append("%'")
                        Case "not-null"         '包含数据
                            sbFilter.Append("NOT ")
                            sbFilter.Append(strField)
                            sbFilter.Append(" IS NULL")
                        Case "null"             '不包含数据
                            sbFilter.Append(strField)
                            sbFilter.Append(" IS NULL")
                        Case "in"
                            sbFilter.Append(strField)
                            sbFilter.Append(" IN (")
                            If strDataType = "money" Then
                                strTemp = Regex.Replace(Trim(xmlNList(i).Attributes.GetNamedItem("value").Value), "(\s|,)+", ",")
                                '替换掉除 0~9 , . - 之外的字符
                                strTemp = Regex.Replace(strTemp, "[^0-9]|[^,.-]", "")
                                sbFilter.Append(strTemp)
                            Else
                                sbFilter.Append("'")
                                strTemp = Regex.Replace(Trim(xmlNList(i).Attributes.GetNamedItem("value").Value), "(\s|,)+", ",")
                                strTemp = strTemp.Replace("'", "''").Replace(",", "','")
                                sbFilter.Append(strTemp)
                                sbFilter.Append("'")
                            End If
                            sbFilter.Append(")")
                        Case "not-in"
                            sbFilter.Append("NOT ")
                            sbFilter.Append(strField)
                            sbFilter.Append(" IN (")
                            If strDataType = "money" Then
                                strTemp = Regex.Replace(Trim(xmlNList(i).Attributes.GetNamedItem("value").Value), "(\s|,)+", ",")
                                '替换掉除 0~9 , . - 之外的字符
                                strTemp = Regex.Replace(strTemp, "[^0-9]|[^,.-]", "")
                                sbFilter.Append(strTemp)
                            Else
                                sbFilter.Append("'")
                                strTemp = Regex.Replace(Trim(xmlNList(i).Attributes.GetNamedItem("value").Value), "(\s|,)+", ",")
                                strTemp = strTemp.Replace("'", "''").Replace(",", "','")
                                sbFilter.Append(strTemp)
                                sbFilter.Append("'")
                            End If
                            sbFilter.Append(")")


                        Case "on"               'On
                            sbFilter.Append(strField)
                            sbFilter.Append("='")
                            sbFilter.Append(xmlNList(i).Attributes.GetNamedItem("value").Value.Replace("'", "''"))
                            sbFilter.Append("'")
                        Case "on-or-after"      'On Or After
                            sbFilter.Append(strField)
                            sbFilter.Append(">='")
                            sbFilter.Append(xmlNList(i).Attributes.GetNamedItem("value").Value.Replace("'", "''"))
                            sbFilter.Append("'")
                        Case "on-or-before"     'On Or Before
                            sbFilter.Append(strField)
                            sbFilter.Append("<='")
                            sbFilter.Append(xmlNList(i).Attributes.GetNamedItem("value").Value.Replace("'", "''"))
                            sbFilter.Append("'")
                        Case "yesterday"        'Yesterday
                            sbFilter.Append("(")
                            sbFilter.Append(strField)
                            sbFilter.Append(">='")
                            sbFilter.Append(Today.AddDays(-1).ToString("yyyy-MM-dd"))
                            sbFilter.Append("' AND ")
                            sbFilter.Append(strField)
                            sbFilter.Append("<'")
                            sbFilter.Append(Today.ToString("yyyy-MM-dd"))
                            sbFilter.Append("')")
                        Case "today"            'Today
                            sbFilter.Append("(")
                            sbFilter.Append(strField)
                            sbFilter.Append(">='")
                            sbFilter.Append(Today.ToString("yyyy-MM-dd"))
                            sbFilter.Append("' AND ")
                            sbFilter.Append(strField)
                            sbFilter.Append("<'")
                            sbFilter.Append(Today.AddDays(1).ToString("yyyy-MM-dd"))
                            sbFilter.Append("')")
                        Case "tomorrow"         'Tomorrow
                            sbFilter.Append("(")
                            sbFilter.Append(strField)
                            sbFilter.Append(">='")
                            sbFilter.Append(Today.AddDays(1).ToString("yyyy-MM-dd"))
                            sbFilter.Append("' AND ")
                            sbFilter.Append(strField)
                            sbFilter.Append("<'")
                            sbFilter.Append(Today.AddDays(2).ToString("yyyy-MM-dd"))
                            sbFilter.Append("')")
                        Case "next-seven-days"  'In The Next 7 Days
                            sbFilter.Append("(")
                            sbFilter.Append(strField)
                            sbFilter.Append(">='")
                            sbFilter.Append(Today.AddDays(1).ToString("yyyy-MM-dd"))
                            sbFilter.Append("' AND ")
                            sbFilter.Append(strField)
                            sbFilter.Append("<'")
                            sbFilter.Append(Today.AddDays(8).ToString("yyyy-MM-dd"))
                            sbFilter.Append("')")
                        Case "last-seven-days"  'In The Last 7 Days
                            sbFilter.Append("(")
                            sbFilter.Append(strField)
                            sbFilter.Append(">='")
                            sbFilter.Append(Today.AddDays(-7).ToString("yyyy-MM-dd"))
                            sbFilter.Append("' AND ")
                            sbFilter.Append(strField)
                            sbFilter.Append("<'")
                            sbFilter.Append(Today.ToString("yyyy-MM-dd"))
                            sbFilter.Append("')")
                        Case "next-week"        'Next Week
                            sbFilter.Append("(")
                            sbFilter.Append(strField)
                            sbFilter.Append(">='")
                            sbFilter.Append(Today.AddDays(7 - Today.DayOfWeek).ToString("yyyy-MM-dd"))
                            sbFilter.Append("' AND ")
                            sbFilter.Append(strField)
                            sbFilter.Append("<'")
                            sbFilter.Append(Today.AddDays(14 - Today.DayOfWeek).ToString("yyyy-MM-dd"))
                            sbFilter.Append("')")
                        Case "last-week"        'Last Week
                            sbFilter.Append("(")
                            sbFilter.Append(strField)
                            sbFilter.Append(">='")
                            sbFilter.Append(Today.AddDays(-7 - Today.DayOfWeek).ToString("yyyy-MM-dd"))
                            sbFilter.Append("' AND ")
                            sbFilter.Append(strField)
                            sbFilter.Append("<'")
                            sbFilter.Append(Today.AddDays(-Today.DayOfWeek).ToString("yyyy-MM-dd"))
                            sbFilter.Append("')")
                        Case "this-week"        'This Week
                            sbFilter.Append("(")
                            sbFilter.Append(strField)
                            sbFilter.Append(">='")
                            sbFilter.Append(Today.AddDays(-Today.DayOfWeek).ToString("yyyy-MM-dd"))
                            sbFilter.Append("' AND ")
                            sbFilter.Append(strField)
                            sbFilter.Append("<'")
                            sbFilter.Append(Today.AddDays(7 - Today.DayOfWeek).ToString("yyyy-MM-dd"))
                            sbFilter.Append("')")
                        Case "next-month"       'Next Month
                            sbFilter.Append("(")
                            sbFilter.Append(strField)
                            sbFilter.Append(">='")
                            sbFilter.Append(Today.AddMonths(1).ToString("yyyy-MM-01"))
                            sbFilter.Append("' AND ")
                            sbFilter.Append(strField)
                            sbFilter.Append("<'")
                            sbFilter.Append(Today.AddMonths(2).ToString("yyyy-MM-01"))
                            sbFilter.Append("')")
                        Case "last-month"       'Last Month
                            sbFilter.Append("(")
                            sbFilter.Append(strField)
                            sbFilter.Append(">='")
                            sbFilter.Append(Today.AddMonths(-1).ToString("yyyy-MM-01"))
                            sbFilter.Append("' AND ")
                            sbFilter.Append(strField)
                            sbFilter.Append("<'")
                            sbFilter.Append(Today.ToString("yyyy-MM-01"))
                            sbFilter.Append("')")
                        Case "this-month"       'This Month
                            sbFilter.Append("(")
                            sbFilter.Append(strField)
                            sbFilter.Append(">='")
                            sbFilter.Append(Today.ToString("yyyy-MM-01"))
                            sbFilter.Append("' AND ")
                            sbFilter.Append(strField)
                            sbFilter.Append("<'")
                            sbFilter.Append(Today.AddMonths(1).ToString("yyyy-MM-01"))
                            sbFilter.Append("')")
                        Case "next-year"        'Next Year
                            sbFilter.Append("year(")
                            sbFilter.Append(strField)
                            sbFilter.Append(")=")
                            sbFilter.Append(Today.Year + 1)
                        Case "last-year"        'Last Year
                            sbFilter.Append("year(")
                            sbFilter.Append(strField)
                            sbFilter.Append(")=")
                            sbFilter.Append(Today.Year - 1)
                        Case "this-year"        'This Year
                            sbFilter.Append("year(")
                            sbFilter.Append(strField)
                            sbFilter.Append(")=")
                            sbFilter.Append(Today.Year)

                        Case "replace"
                            '检查返回值的有效性
                            '   不允许出现 INSERT、UPDATE、DELETE、CREATE、ALTER、DROP 等关键字
                            '   不允许执行系统存储过程（以 sp_ 或 xp_ 开头）
                            '   不允许出现“ INTO ”，以防执行“SELECT ... INTO ...”
                            Dim r As New Regex("insert\s|update\s|delete\s|create\s|alter\s|drop\s|(exec|execute)[\s\(]|sp_|xp_|\sinto\s", RegexOptions.IgnoreCase)
                            Dim strReplace = xmlNList(i).Attributes.GetNamedItem("value").Value
                            If r.IsMatch(strReplace) Then
                                sbFilter.Append("1=1")
                            Else
                                sbFilter.Append(strReplace)
                            End If

                        Case Else
                            sbFilter.Append("1=1")
                    End Select

                Case "filter"
                    '递归
                    sbFilter.Append(GetFilter(xmlNList(i), strEntity, strBUGUID, strUserGUID))
            End Select
        Next
        sbFilter.Append(") and ")

        Return sbFilter.ToString
    End Function

    '替换关键字
    Public Function ReplaceKeywords2(ByVal s As String, ByVal strBUGUID As String, ByVal strUserGUID As String, Optional ByVal quot As String = "") As String

        s = s.Replace("[今天]", Today.ToString("yyyy-MM-dd") + quot)
        s = s.Replace("[本周初]", quot + Today.AddDays(CInt(1 - Today.DayOfWeek)).ToString("yyyy-MM-dd") + quot)
        s = s.Replace("[本周末]", quot + Today.AddDays(CInt(7 - Today.DayOfWeek)).ToString("yyyy-MM-dd") + quot)
        s = s.Replace("[一周前]", quot + Today.AddDays(-7.0).ToString("yyyy-MM-dd") + quot)
        s = s.Replace("[本月初]", quot + Today.ToString("yyyy-MM-01") + quot)
        s = s.Replace("[本月末]", quot + Today.AddMonths(1).AddDays(CInt(0 - Today.Day)).ToString("yyyy-MM-dd") + quot)
        s = s.Replace("[一个月前]", quot + Today.AddMonths(-1).ToString("yyyy-MM-dd") + quot)
        s = s.Replace("[本年初]", quot + Today.ToString("yyyy-01-01") + quot)
        s = s.Replace("[本年末]", quot + Today.AddYears(1).AddMonths(1 - Today.Month).AddDays(CInt(0 - Today.Day)).ToString("yyyy-MM-dd") + quot)
        s = s.Replace("[当前公司]", strBUGUID)
        s = s.Replace("[本人]", strUserGUID)

        Return s

    End Function
    Friend Function getQueryString(ByVal attribute As String, ByVal value As String, ByVal caseCode As String, ByVal strBUGUID As String, ByVal strUserGUID As String) As String
        Dim TableName, FieldName As String

        ' 获取表名
        TableName = ""
        If Trim(attribute) <> "" Then
            Dim Attributes() As String = attribute.Split(".")
            If Attributes.Length > 1 Then
                TableName = Attributes(0)
                FieldName = Attributes(1)
            Else
                FieldName = Attributes(0)
            End If
        End If

        ' 执行分支函数，获取过滤表达式
        Select Case caseCode
            Case "xmdj"
                Return GetProjectFilterExp(strUserGUID, Nothing, Nothing, Nothing, False)
            Case "allprojectofslxt"
                ' 售楼系统中返回当前公司、当前用户的所有项目过滤表达式
                ' 由于支持前端定义字段，所以这里要将 ProjGUID 替换成前端定义的内容
                Return GetProjectFilterExp(strUserGUID, TableName, strBUGUID, "0101").Replace("ProjGUID", FieldName)

            Case "allprojectofkfxt"
                ' 客服系统中返回当前公司、当前用户的所有项目过滤表达式
                ' 由于支持前端定义字段，所以这里要将 ProjGUID 替换成前端定义的内容
                Return GetProjectFilterExp(strUserGUID, TableName, strBUGUID, "0102").Replace("ProjGUID", FieldName)

            Case "buildsofyuan"
                ' 返回分区下楼栋过滤表达式
                Dim SQL As String
                Dim DT As DataTable
                Dim i As Integer
                Dim sb As New StringBuilder

                SQL = "SELECT BldGUID FROM p_Building WHERE CHARINDEX('" & value & ".', ParentCode + '.') = 1"
                DT = MyDB.GetDataTable(SQL)
                If DT.Rows.Count > 0 Then
                    If TableName = "" Then
                        sb.Append(FieldName & " IN (")
                    Else
                        sb.Append(TableName & "." & FieldName & " IN (")
                    End If

                    For i = 0 To DT.Rows.Count - 1
                        If i = 0 Then
                            sb.Append("'" & DT.Rows(i)("BldGUID").ToString & "'")
                        Else
                            sb.Append(",'" & DT.Rows(i)("BldGUID").ToString & "'")
                        End If
                    Next

                    sb.Append(")")

                    Return sb.ToString
                Else
                    Return "(1=2)"
                End If

        End Select
    End Function

    Public Function GetProjectFilterExp(ByVal userGUID As String, ByVal tableName As String, ByVal buGUID As String, ByVal application As String) As String
        Return GetProjectFilterExp(userGUID, tableName, buGUID, application, True)
    End Function

    Public Function GetProjectFilterExp(ByVal userGUID As String, ByVal tableName As String, ByVal buGUID As String, ByVal application As String, ByVal isUserFilter As Boolean) As String
        Dim UserProjects As String

        UserProjects = GetProjectString(userGUID, buGUID, application, isUserFilter)     ' 取用户项目
        If UserProjects = "" Then
            Return "(1=2)"

        Else
            If tableName = "" Or tableName Is Nothing Then
                Return "ProjGUID in (" & UserProjects & ")"

            Else
                Return tableName & ".ProjGUID in (" & UserProjects & ")"

            End If

        End If

    End Function

    Public Function GetProjectString(ByVal userGUID As String, ByVal buGUID As String, ByVal application As String, ByVal isUserFilter As Boolean) As String
        If userGUID = "" Then
            Return ""
        End If

        Dim SQL As String
        Dim sb As New StringBuilder
        Dim DT As DataTable
        Dim i As Integer

        SQL = "SELECT IsAllProj,UserProjects FROM myUser WHERE UserGUID='" & userGUID & "'"
        DT = MyDB.GetDataTable(SQL)
        If DT.Rows.Count > 0 Then
            SQL = "SELECT DISTINCT mainTable.ProjGUID "

            ' 是否允许操作所有项目
            If DT.Rows(0)("IsAllProj") Then     ' 如果允许操作所有项目
                SQL += " FROM " & IIf(application = "0101", " ep_Project mainTable ", " ek_Project mainTable ") & _
                       " RIGHT JOIN myBusinessUnit bu ON mainTable.BUGUID = bu.BUGUID " & _
                       " WHERE CHARINDEX((select HierarchyCode+'.' from myUser " & _
                       " left join myBusinessUnit on myUser.BUGUID = myBusinessUnit.BUGUID " & _
                       " where UserGUID='" & userGUID & "'), bu.HierarchyCode+'.')=1 " & _
                       " AND bu.IsEndCompany=1 AND bu.IsCompany=1 AND mainTable.ProjGUID IS NOT NULL "

            Else     ' 如果不允许操作所有项目
                SQL += " FROM myTeamUsers mainTable " & _
                       IIf(application = "0101", " INNER JOIN ep_Project p ON (mainTable.ProjGUID = p.ProjGUID AND mainTable.BUGUID = p.BUGUID) ", "") & _
                       " WHERE mainTable.UserGUID='" & userGUID & "' AND mainTable.ProjGUID IS NOT NULL "
            End If

            ' 用户过滤项目 
            If isUserFilter Then
                If DT.Rows(0)("UserProjects").ToString = "[NULL]" Then          ' 如果用户过滤项目为空
                    Return ""

                ElseIf DT.Rows(0)("UserProjects").ToString = "[ALL]" Then       ' 如果用户过滤项目为所有项目

                Else        ' 如果用户自己过滤了一些项目
                    Dim DTProject As DataTable

                    DTProject = MyDB.GetDataTable("SELECT ProjGUID FROM myUserProjects WHERE UserGUID='" & userGUID & "'")
                    If DTProject.Rows.Count > 0 Then
                        SQL += " AND mainTable.ProjGUID in ("

                        For i = 0 To DTProject.Rows.Count - 1
                            If i = 0 Then
                                SQL += "'" & DTProject.Rows(i)("ProjGUID").ToString & "'"
                            Else
                                SQL += ",'" & DTProject.Rows(i)("ProjGUID").ToString & "'"
                            End If
                        Next

                        SQL += ")"

                    Else
                        Return ""

                    End If

                End If

            End If

            ' 公司过滤
            If buGUID <> "" Then         ' 如果带公司过滤
                SQL += " AND mainTable.BUGUID='" & buGUID & "'"
            End If

            DT = MyDB.GetDataTable(SQL)
            If DT.Rows.Count > 0 Then
                For i = 0 To DT.Rows.Count - 1
                    If i = 0 Then
                        sb.Append("'" & DT.Rows(i)("ProjGUID").ToString & "'")

                    Else
                        sb.Append(",'" & DT.Rows(i)("ProjGUID").ToString & "'")

                    End If
                Next

                Return sb.ToString

            Else
                Return ""

            End If

        Else
            Return ""

        End If

    End Function
End Class
